package models;

import models.constants.DeletedStatus;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import product.ProductSpec;
import util.common.ConvertUtil;
import util.common.UStringUtil;

import javax.persistence.*;
import  java.util.* ;

/**
 * Created by liming on 2016/12/26.
 */
@Entity
@Table(name = "admin_stock_in_plan_setup_item")
public class AdminStockInPlanSetupItem extends BaseModel {
    /**
     * 采购计划
     */
    @ManyToOne
    @JoinColumn(name = "admin_stock_in_plan_setup_id")
    public AdminStockInPlanSetup adminStockInPlanSetup;

    /**
     * 规格产品
     */
    @ManyToOne
    @JoinColumn(name = "product_spec_id")
    public ProductSpec productSpec;

    /**
     * 次日需求备货量
     */
    @Column(name = "needs_num")
    public Double needsNum;

    /**
     * 备注
     */
    @Column(name = "remark")
    public String remark;


    //获取差异小于0 或者 差异小于 备货量 的产品
    public final static String PRODUCT_SPEC_STOCK_LESS_0 = " " +
            "  select * from   (SELECT " +
            "      a.id, " +
            "      b. NAME, " +
            "      c. NAME AS type_name, " +
            "      a.spec, " +
            "      a.unit, " +
            "      a.d_code, " +
            "      a.color_mark, " +
            "      round(ifnull(d.day_sale, 0), 2)AS day_sale, " +
            "      round( " +
            "        IF( " +
            "          ifnull(d.day_sale, 0)= 0, " +
            "          0, " +
            "          ifnull(e.stock_num, 0)/ d.day_sale " +
            "        ), " +
            "        2 " +
            "      )AS lasting_days, " +
            "      ifnull(e.stock_num, 0)AS stock_num, " +
            "      ifnull(f.total_sale_num, 0)AS total_sale_num, " +
            "      ifnull(e.stock_num, 0)- ifnull(f.total_sale_num, 0)AS diff_num, " +
            "    IF(ifnull(d.day_sale, 0) = 0, 1, 0)AS base_order , " +
            "    g.id as setUpItemId , " +
            "    ifnull(g.needs_num , 0) as needsNum , " +
            "    g.admin_stock_in_plan_setup_id as setUpId ," +
            "    c.is_get_specs " +
            "    FROM " +
            "      product_specs a " +
            "    LEFT JOIN products b ON a.product_id = b.id " +
            "    LEFT JOIN admin_product_type c ON b.admin_product_type_id = c.id " +
            "    LEFT JOIN( " +
            "      SELECT " +
            "        a.split_product_spec_id, " +
            "        sum(splite_num)/ datediff(now(), min(a.create_at))AS day_sale " +
            "      FROM " +
            "        v_admin_order_splite a " +
            "      WHERE " +
            "        a.to_track = 1 " +
            "      AND a.admin_order_status < 100 " +
            "      AND a.create_at >= date_sub(curdate(), INTERVAL 7 DAY) " +
            "      AND a.create_at < curdate() " +
            "      AND a.deleted = 0 " +
            "      AND a.to_track = 1 " +
            "      GROUP BY " +
            "        a.split_product_spec_id " +
            "    )d ON a.id = d.split_product_spec_id " +
            "    LEFT JOIN admin_product_spec_stock e ON a.id = e.product_spec_id " +
            "    LEFT JOIN( " +
            "      SELECT " +
            "        a.split_product_spec_id, " +
            "        sum(a.splite_num)AS total_sale_num " +
            "      FROM " +
            "        v_admin_order_splite a " +
            "      WHERE " +
            "        a.to_track = 1 " +
            "      AND a.admin_order_status < 50 " +
            "      AND a.logistics_status = 'PREPARING' " +
            "      AND a.deleted = 0 " +
            "      AND a.to_track = 1 " +
            "      GROUP BY " +
            "        a.split_product_spec_id " +
            "    )f ON a.id = f.split_product_spec_id " +
            "  left join  ( select a.id ,a.admin_stock_in_plan_setup_id , a.needs_num ,a.product_spec_id, a.create_at " +
            "                from admin_stock_in_plan_setup_item a " +
            "                 where date_format(a.create_at,'%%y-%%m-%%d') = date_sub(curdate(),interval 1 day) " +
            "              ) g on g.product_spec_id = a.id  " +
            "    WHERE " +
            "      a.deleted = 0 " +
            "    AND a.disable_status = 'ENABLE' " +
            "    AND( " +
            "      a.product_classify = 'BASIC_PRODUCT' " +
            "      OR a.product_classify = 'FINISHED_PRODUCT' " +
            "      OR a.product_classify = 'ACCESSORY' " +
            "    ) " +
            "  ) a  %s" ;

    public static List<Map<String,Object>> needPurchaseList(){

        String sqlWhere = "";
        AdminStockInPlanSetup planSetup = AdminStockInPlanSetup.getByCreateAt();
        if(planSetup != null) {
            sqlWhere = " where ( a.diff_num < 0 and a.is_get_specs < 2 )or ( a.diff_num < a.needsNum and a.setUpId = "+ ConvertUtil.toLong(planSetup.id)+" ) " ;
        } else {
            sqlWhere = " where a.diff_num < 0  and a.is_get_specs < 2" ;
        }

        String sqlSelect = String.format(PRODUCT_SPEC_STOCK_LESS_0, sqlWhere);
        Query query = ProductSpec.em().createNativeQuery(sqlSelect);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String,Object>> resultList = query.getResultList();

        return resultList;
    }
    //获取采购计划的产品列表
    public  static List<AdminStockInPlanSetupItem> loadStockInPlanSetupItemList(long id){
        return AdminStockInPlanSetupItem.find("adminStockInPlanSetup.id=? and adminStockInPlanSetup.deletedStatus=?",id, DeletedStatus.UN_DELETED).fetch();
    }
    //删除
    public static void deletedItem(Integer[] delItemIdArray){
        String delIdStr = UStringUtil.concatStr(",", delItemIdArray);
        AdminStockInPlanSetupItem.delete("id in (" + delIdStr + ")");

    }


}
